package srdb.migration.common;

import srdb.migration.Utility;
import srdb.migration.schema.metadata.Column;
import srdb.migration.schema.metadata.Constraint;
import srdb.migration.schema.metadata.DataType;
import srdb.migration.schema.metadata.Domain;
import srdb.migration.schema.metadata.Function;
import srdb.migration.schema.metadata.Grant;
import srdb.migration.schema.metadata.Group;
import srdb.migration.schema.metadata.Index;
import srdb.migration.schema.metadata.Package;
import srdb.migration.schema.metadata.Procedure;
import srdb.migration.schema.metadata.Role;
import srdb.migration.schema.metadata.Rule;
import srdb.migration.schema.metadata.Synonym;
import srdb.migration.schema.metadata.Table;
import srdb.migration.schema.metadata.Trigger;
import srdb.migration.schema.metadata.User;
import srdb.migration.schema.metadata.View;

public class MTKGenericTransformDDL extends MTKTransformDDL {
	public String getCreateScript(Table table) {
		String sql = "CREATE TABLE " + escape(table.getName()) + " (\n";
		for (int i = 0; i < table.getColumns().size(); i++) {
			sql = sql + getColumnLine(table.getColumns().get(i));
			if (i < table.getColumns().size() - 1)
				sql = sql + ", ";
			sql = sql + "\n";
		}
		sql = sql + ");\n";

		sql = sql + getColumnsComments(table);

		return sql;
	}

	public String getCreateScript(View view) {
		String sql = "CREATE VIEW " + escape(view.getName()) + " AS \n";
		sql = sql + view.getSQL() + "\n";

		return sql;
	}

	protected String getColumnsComments(Table table) {
		String sql = "";
		Column column = null;
		for (int i = 0; i < table.getColumns().size(); i++) {
			column = table.getColumns().get(i);
			if (column.getComments() != null) {
				sql = sql
						+ "COMMENT ON COLUMN "
						+ escape(table.getTargetSchemaName())
						+ "."
						+ escape(table.getName())
						+ "."
						+ escape(column.getName(), column.isCaseSensitive())
						+ " IS '"
						+ table.getColumns().get(i).getComments()
								.replaceAll("'", "\\\\'") + "';\n";
			}

		}

		return sql;
	}

	protected String getColumnLine(Column column) {
		String str = "";
		String colName = column.getName();

		colName = escape(colName, column.isCaseSensitive());

		str = str + getIndent() + colName;

		String customType = null;

		if ((customType = super.getCustomColType(column.getTableName(),
				column.getName())) != null) {
			str = str + " " + customType;
		} else {
			if (column.isRowID()) {
				str = str + " " + "VARCHAR";
			} else if (column.isInterval())
				str = str + " " + "INTERVAL";
			else if (column.isGUID())
				str = str + " UUID";
			else {
				str = str + " " + column.getDataTypeString();
			}

			if ((column.getDataType() == Column.DataType.VARCHAR)
					|| (column.getDataType() == Column.DataType.NVARCHAR)) {
				str = str + "(" + column.getLength() + ")";
			} else if (((column.getDataType() == Column.DataType.INTEGER)
					&& (!column.getDataTypeString().equalsIgnoreCase("INTEGER"))
					&& (!column.isSmallint())
					&& (!column.getDataTypeString().equalsIgnoreCase("INT"))
					&& (!column.getDataTypeString().equalsIgnoreCase("BIGINT"))
					&& (!column.getDataTypeString().equalsIgnoreCase("SERIAL"))
					&& (!column.getDataTypeString().equalsIgnoreCase(
							"BIGSERIAL")) && (!column.getDataTypeString()
					.equalsIgnoreCase("YEAR")))
					|| ((column.getDataType() == Column.DataType.NUMERIC) && (!column
							.isReal()))) {
				if (column.getPrecision() > 0) {
					str = str + "(" + column.getPrecision();
					if (column.getScale() > 0)
						str = str + "," + column.getScale();
					str = str + ")";
				}

			}

		}

		if ((column.getDefaultClause() != null)
				&& (column.getDefaultClause().trim().length() > 0)) {
			String defaultValue = column.getDefaultClause();
			if ((defaultValue != null)
					&& (defaultValue.toLowerCase().contains("current"))) {
				defaultValue = "now";
			}

			if (column.isTimeStamp()) {
				if (this.defaultDateTime != null) {
					defaultValue = this.defaultDateTime.replace("_", " ");
					if (defaultValue.length() == 10) {
						defaultValue = defaultValue + " 00:00:00";
					}

				} else if (defaultValue.equalsIgnoreCase("0000-00-00 00:00:00")) {
					try {
						defaultValue = Utility
								.formatMySQLZeroDateTimeToPG(defaultValue);
					} catch (Exception exp) {
					}
				}
			}
			if (column.isDate()) {
				if (this.defaultDateTime != null) {
					defaultValue = this.defaultDateTime.substring(0, 10);
				} else if (defaultValue.equalsIgnoreCase("0000-00-00")) {
					try {
						defaultValue = Utility
								.formatMySQLZeroDateTimeToPG(defaultValue);
					} catch (Exception exp) {
					}
				}
			}
			if (!defaultValue.trim().toUpperCase().startsWith("DEFAULT ")) {
				str = str + " DEFAULT '" + defaultValue + "'";
			} else {
				defaultValue = replaceDefaultClauseEnclosingDoubleQuotes(defaultValue);
				str = str + " " + defaultValue;
			}
		}

		if (!column.isNullable()) {
			str = str + " NOT NULL";
		}
		return str;
	}

	public String getCreateScript(Index index) {
		String sql = "";

		sql = sql + "CREATE " + (index.isUnique() ? "UNIQUE" : "") + " INDEX "
				+ escape(index.getName()) + " ON "
				+ escape(index.getTableName()) + " (";

		for (int i = 0; i < index.getColumns().size(); i++) {
			sql = sql
					+ escape(index.getColumns().get(i).getName(), index
							.getColumns().get(i).isCaseSensitive());

			if (i < index.getColumns().size() - 1)
				sql = sql + ", ";
		}
		sql = sql + ");\n";
		return sql;
	}

	public String getCreateScript(Constraint constraint) {
		String sql = "";

		String constTableName = constraint.getTableName();
		constTableName = escape(constTableName);

		String constraintName = escape(constraint.getName(),
				constraint.isCaseSensitive());
		if (constraint.getType() == 'P') {
			sql = sql + "ALTER TABLE " + constTableName;

			sql = sql + " ADD";
			if ((constraintName != null)
					&& (!constraintName.equalsIgnoreCase("PRIMARY"))
					&& (!constraintName.equalsIgnoreCase("\"PRIMARY\""))) {
				sql = sql + " CONSTRAINT " + constraintName;
			}
			sql = sql + " PRIMARY KEY (";
			for (int i = 0; i < constraint.getColumns().size(); i++) {
				sql = sql
						+ escape(constraint.getColumns().get(i).getName(),
								constraint.getColumns().get(i)
										.isCaseSensitive());

				if (i < constraint.getColumns().size() - 1)
					sql = sql + ", ";
			}
			sql = sql + ");\n";
		} else if (constraint.getType() == 'U') {
			sql = sql + "ALTER TABLE " + constTableName + " ADD";
			if (constraintName != null) {
				sql = sql + " CONSTRAINT " + constraintName;
			}
			sql = sql + " UNIQUE (";
			for (int i = 0; i < constraint.getColumns().size(); i++) {
				sql = sql
						+ escape(constraint.getColumns().get(i).getName(),
								constraint.getColumns().get(i)
										.isCaseSensitive());

				if (i < constraint.getColumns().size() - 1)
					sql = sql + ", ";
			}
			sql = sql + ");\n";
		} else if (constraint.getType() == 'C') {
			if (!constraint.getSearchCondition().endsWith("IS NOT NULL")) {
				sql = sql + "ALTER TABLE " + constTableName + " ADD";

				if (constraintName != null) {
					sql = sql + " CONSTRAINT " + constraintName;
				}

				String constCondition = constraint.getSearchCondition();

				if (!constCondition.trim().toUpperCase().startsWith("CHECK "))
					sql = sql + " CHECK (" + constCondition + ")";
				else {
					sql = sql + constCondition;
				}

				sql = sql + ";\n";
			}
		} else if (constraint.getType() == 'R') {
			sql = sql + "ALTER TABLE " + constTableName + " ADD";
			if (constraintName != null)
				sql = sql + " CONSTRAINT " + constraintName;
			sql = sql + " FOREIGN KEY (";
			for (int i = 0; i < constraint.getColumns().size(); i++) {
				sql = sql
						+ escape(constraint.getColumns().get(i).getName(),
								constraint.getColumns().get(i)
										.isCaseSensitive());

				if (i < constraint.getColumns().size() - 1)
					sql = sql + ", ";
			}
			sql = sql + ") REFERENCES "
					+ escape(constraint.getRefIntegrityTable()) + " (";

			for (int i = 0; i < constraint.getRefIntegrityColumns().size(); i++) {
				sql = sql
						+ escape(constraint.getRefIntegrityColumns().get(i)
								.getName(), constraint.getRefIntegrityColumns()
								.get(i).isCaseSensitive());

				if (i < constraint.getRefIntegrityColumns().size() - 1)
					sql = sql + ", ";
			}
			sql = sql + ") MATCH FULL ON DELETE " + constraint.getDeleteRule()
					+ ";\n";
		}

		return sql;
	}

	private String escape(String identifier, boolean caseSensitive) {
		if ((identifier != null) && (identifier.startsWith("\""))
				&& (identifier.endsWith("\""))) {
			return identifier;
		}
		if (!caseSensitive) {
			return Utility.escape(identifier,
					MTKConstants.DATABASE_TYPE.ENTERPRISEDB);
		}

		return "\"" + identifier + "\"";
	}

	private String escape(String identifier) {
		return escape(identifier, false);
	}

	public String getCreateScript(Procedure proc) {
		return "";
	}

	public String getCreateScript(Function func) {
		return "";
	}

	public String getCreateScript(Synonym syn) {
		return "";
	}

	public String getCreateScript(Trigger trigger) {
		return "";
	}

	public String getCreateScriptPackageSpec(Package pack) {
		return "";
	}

	public String getCreateScriptPackageBody(Package pack) {
		return "";
	}

	public String getCreateScript(Role role) {
		throw new UnsupportedOperationException("Not supported yet.");
	}

	public String getCreateScript(User user) {
		throw new UnsupportedOperationException("Not supported yet.");
	}

	public String getCreateScript(Grant grant) {
		throw new UnsupportedOperationException("Not supported yet.");
	}

	private String replaceDefaultClauseEnclosingDoubleQuotes(
			String defaultClause) {
		if (defaultClause != null) {
			String[] arrTemp = defaultClause.trim().split(" ");
			int valIndex = arrTemp.length - 1;

			if ((arrTemp[valIndex].startsWith("\""))
					&& (arrTemp[valIndex].endsWith("\""))) {
				defaultClause = arrTemp[0]
						+ " '"
						+ arrTemp[valIndex].substring(1,
								arrTemp[valIndex].length() - 1) + "'";
			}
		}

		return defaultClause;
	}

	public String getCreateScript(Rule rule) {
		if (rule == null) {
			return null;
		}
		return rule.getDefinition();
	}

	public String getCreateScript(Group group) {
		throw new UnsupportedOperationException("Not supported yet.");
	}

	public String getCreateScript(Domain domain) {
		throw new UnsupportedOperationException("Not supported yet.");
	}

	public String getCreateScript(DataType type) {
		throw new UnsupportedOperationException("Not supported yet.");
	}
}

/*

 */